<?php
class Ranting_model extends CI_Model{

	function   __construct() {
        parent::__construct();
    }

    function searchUnit($keyword){

	   $query = $this->db->query("select * from unit order by intid_unit asc");
	   return $query->result();
    }
	/*
	function rekursif_pracm($strkode_dealer,$bulan,$bulanke){
		$data	=	array();
		$dataTemp	=	array();
		$query = $this->db->query("select member.strkode_dealer,
		member.strnama_dealer,
			(select sum(n.intomset10) 
				from nota n where n.intid_week in (select intid_week from week where intbulan <= '".$bulan."' and intbulan >= '".$bulanke."') and
				n.intid_dealer = member.intid_dealer and 
				(n.intid_jpenjualan = 1 or n.intid_jpenjualan = 9) and
				n.is_dp = 0) intomset10, 
			(select sum(n.intomset20) 
				from nota n where n.intid_week in (select intid_week from week where intbulan <= '".$bulan."' and intbulan >= '".$bulanke."') and
				n.intid_dealer = member.intid_dealer and 
				(n.intid_jpenjualan = 1 or n.intid_jpenjualan = 9) and
				n.is_dp = 0) intomset20 
			from member 
			where member.strkode_upline = '".$strkode_dealer."' ");
		
		//
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				//masuki ke rekursif
				$data[] = array(
						'strkode_dealer' => $row->strkode_dealer,
						'strnama_dealer' => $row->strnama_dealer,
						'intomset10'	=>	$row->intomset10,
						'intomset20'	=>	$row->intomset20,
					);
				
				$dataTemp	=	$this->rekursif_pracm($row->strkode_dealer,$bulan,$bulanke);
				if(!empty($dataTemp)){
					for($i=0;$i<sizeof($dataTemp);$i++){
						$data[]	=	array(
							'strkode_dealer' => $dataTemp[$i]['strkode_dealer'],
							'strnama_dealer' => $dataTemp[$i]['strnama_dealer'],
							'intomset10'	=>	$dataTemp[$i]['intomset10'],
							'intomset20'	=>	$dataTemp[$i]['intomset20'],
							);
						}
				}
			}
			return $data;
		}else{
			// eksekusi mati
			return "";
		}
	}
	*/
	function get_datacalon_manager($strkode_dealer){
		$query = $this->db->query('select * from calon_manager where strkode_dealer = "'.$strkode_dealer.'" group by strkode_dealer');
		return $query->result();
	}
	 function get_Omset_Pengejaran($kode, $bulan,$bulanke) {
      $query = $this->db->query("
	  select sum(nota.intomset10) omset10, 
	  	sum(nota.intomset20) omset20, 
		sum(nota.inttotal_omset) total_omset, 
		member.strnama_dealer, 
		unit.strnama_unit, 
		unit.intid_unit,
		cabang.strnama_cabang
	  from (select intid_dealer,intid_cabang,strnama_dealer,intid_unit from member where strkode_dealer = '".$kode."')as member inner join 
	  (select * from nota where is_dp = 0 and (intid_jpenjualan = 1 or intid_jpenjualan = 9) and intid_week in (select intid_week from week where intbulan <= '".$bulan."' and intbulan >= '".$bulanke."')) as nota on nota.intid_dealer = member.intid_dealer inner join
	  unit on unit.intid_unit = member.intid_unit inner join 
	  cabang on cabang.intid_cabang = member.intid_cabang 
		");
        return $query->result();
     }
	 function get_Anak_Pengejaran($kode, $bulan, $bulanke) {
         $thn = date('Y');
	     $query = $this->db->query("select 
		 	member.strnama_upline,
			member.strnama_dealer,
			member.strkode_dealer,
			member.intid_dealer
		from member
			where member.strkode_upline = '$kode'
			group by member.strnama_dealer");
        return $query->result();
     }
	 //21 juni2013 
	 //author ikhlas
	  function rekursif_pracm($strkode_dealer,$bulan,$bulanke){
		$data	=	array();
		$dataTemp	=	array();
		$temp	=	array();
		$query = $this->db->query("select 
		member.strkode_dealer,
		member.strnama_dealer,
			(select sum(n.intomset10) 
				from nota n where n.intid_week in (select intid_week from week where intbulan <= '".$bulan."' and intbulan >= '".$bulanke."') and
				n.intid_dealer = member.intid_dealer and 
				(n.intid_jpenjualan = 1 or n.intid_jpenjualan = 9) and
				n.is_dp = 0) intomset10, 
			(select sum(n.intomset20) 
				from nota n where n.intid_week in (select intid_week from week where intbulan <= '".$bulan."' and intbulan >= '".$bulanke."') and
				n.intid_dealer = member.intid_dealer and 
				(n.intid_jpenjualan = 1 or n.intid_jpenjualan = 9) and
				n.is_dp = 0) intomset20
			from member left join calon_manager on member.strkode_dealer = calon_manager.strkode_dealer 
			where member.strkode_upline = '".$strkode_dealer."' 
				and calon_manager.strkode_dealer != '".$strkode_dealer."'
			");
		if($query->num_rows() > 0 ){
			foreach($query->result() as $row){
				if($row->intomset20 != ""){
					$temp[1]	=	$row->intomset20;
				}else{
					$temp[1]	=	0;
				}
				if($row->intomset10 != ""){
					$temp[2]	=	$row->intomset10;
				}else{
					$temp[2]	=	0;
				}
				$temp[3]	=	($temp[1] * 0.8) +($temp[2] * 0.9);
				$temp[4]	=	$temp[3] * 0.03;
				$temp[5]	=	$temp[4] * 0.05;
				$temp[6]	=	$temp[4]	-	$temp[5];
				if(!empty($row->strnama_dealer)){		
					$data[]	=	array(
						'strnama_dealer' => $row->strnama_dealer,
						'intomset20' => $temp[1],
						'intomset10' => $temp[2],
						'harga_dealer' => $temp[3],
						'or3' => $temp[4],
						'pph5' => $temp[5],
						'terima' => $temp[6],
						);
						//echo "j, ".$j."". $row->strnama_dealer."<br />";
						$this->rekursif_pracm($row->strkode_dealer,$bulan,$bulanke);
					}
					//siap-siap rekursif
					//return $data;
				}
		}
		//pengecekan keluar data atau tidak
				
				for($i = 0; $i < sizeof($data);$i++){
					$temp[0]	=	$data[$i]['strnama_dealer'];
					$temp[1]	=	$data[$i]['intomset20'];
					$temp[2]	=	$data[$i]['intomset10'];
					$temp[3]	=	$data[$i]['harga_dealer'];
					$temp[4]	=	$data[$i]['or3'];
					$temp[5]	=	$data[$i]['pph5'];
					$temp[6]	=	$data[$i]['terima'];
					
					echo	"<tr>";
					echo	"<td>	".$temp[0]."</td>";
					echo	"<td>".$temp[1]."</td>";
					echo	"<td>".$temp[2]."</td>";
					echo	"<td>".$temp[3]."</td>";
					echo	"<td>".$temp[4]."</td>";
					echo	"<td>".$temp[5]."</td>";
					echo	"<td>".$temp[6]."</td>";
					echo	"</tr>";
				}
		}
		
		function rekrusifomset_ver2($strkode_dealer,$bulan,$bulanke,$omset){
			$nominal	=	0;
			$query2	=	$this->db->query("select 
				(select sum(nota.intomset10) from nota 
					where nota.is_dp = 0 and (nota.intid_jpenjualan=1 or nota.intid_jpenjualan = 9) 
					and nota.intid_week in (select intid_week from week where intbulan <= ".$bulan." and intbulan >= ".$bulanke.")
					and member.intid_dealer = nota.intid_dealer) intomset10,
				(select sum(nota.intomset20) from nota 
					where nota.is_dp = 0 and (nota.intid_jpenjualan=1 or nota.intid_jpenjualan = 9) 
					and nota.intid_week in (select intid_week from week where intbulan <= ".$bulan." and intbulan >= ".$bulanke.")
					and member.intid_dealer = nota.intid_dealer) intomset20,
				member.strkode_dealer,member.strnama_dealer
					from member left join calon_manager on member.strkode_dealer = calon_manager.strkode_dealer
					where member.strkode_upline = '".$strkode_dealer."'
					and calon_manager.strkode_dealer != ''
					");
			foreach($query2->result() as $row){
				if($omset == "omset20"){
					$nominal += $row->intomset20;
					}
				if($omset == "omset10"){
					$nominal += $row->intomset10;
				}
				$nominal += $this->rekrusifomset_ver2($row->strkode_dealer,$bulan,$bulanke,$omset);
				//echo "- : ".$nominal." ".$row->strnama_dealer."<br />";
			}
			return $nominal;
		}
}
?>
